"""
Case Type   : 兼容性
Case Name   : pipelined函数结合gs_dump工具测试
Create At   : 2024/10/8
Owner       : wan005
Description :
    1.创建数据库
    2.创建pipelined函数
    3.使用gs_dump导出函数
    4.使用gs_restore恢复函数
    5.调用pipelined函数
    6.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
History     :
"""
import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class Tools(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH()
        self.common = Common()
        self.constant = Constant()
        self.dbuser_node = Node('PrimaryDbUser')
        self.root_node = Node('PrimaryRoot')
        self.dump_db_01 = 'db_pipelined_30_1'
        self.dump_db_02 = 'db_pipelined_30_2'
        self.dump_path = os.path.join(macro.DB_INSTANCE_PATH, 'dump_file')

    def test_flush(self):
        self.log.info('-----step1:创建数据库；expect：成功-----')
        sql_cmd1 = f'drop database if exists {self.dump_db_01};' \
                f'create database {self.dump_db_01} with dbcompatibility \'A\';'
        self.log.info(sql_cmd1)
        create_res1 = self.pri_sh.execut_db_sql(sql_cmd1)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, create_res1,
                      '数据库创建失败')
        sql_cmd2 = f'drop database if exists {self.dump_db_02};' \
                f'create database {self.dump_db_02} with dbcompatibility \'A\';'
        self.log.info(sql_cmd2)
        create_res2 = self.pri_sh.execut_db_sql(sql_cmd2)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, create_res2,
                      '数据库创建失败')

        self.log.info('-----step2:创建pipelined函数；expect：成功-----')
        sql_cmd3 = f'create or replace type type_0030 as (c1 integer, ' \
                   f'c2 tinyint, c3 numeric(10, 4), c4 decimal(12), ' \
                   f'c5 number(10,4), c6 float(10), c7 bit(1), c8 dec(10,3),' \
                   f' c9 boolean, c10 char(10), c11 nchar(10), c12 varchar(' \
                   f'200), c13 varchar2(20), c14 text, c15 blob, c16 date, ' \
                   f'c17 time, c18 interval);'
        sql_res3 = self.pri_sh.execut_db_sql(sql_cmd3, dbname=self.dump_db_01)
        self.log.info(sql_res3)
        self.assertIn(self.constant.CREATE_TYPE_SUCCESS_MSG, sql_res3,
                      '创建失败')
        sql_cmd4 = f'create or replace type tb_type_0030 as table of type_0030;'
        sql_res4 = self.pri_sh.execut_db_sql(sql_cmd4, dbname=self.dump_db_01)
        self.log.info(sql_res4)
        self.assertIn(self.constant.CREATE_TYPE_SUCCESS_MSG, sql_res4,
                      '创建失败')
        sql_cmd5 = f'create type varray_type_0030_1 as varray(10) of numeric;'
        sql_res5 = self.pri_sh.execut_db_sql(sql_cmd5, dbname=self.dump_db_01)
        self.log.info(sql_res5)
        self.assertIn(self.constant.CREATE_TYPE_SUCCESS_MSG, sql_res5,
                      '创建失败')
        sql_cmd6 = f'create type varray_type_0030_2 as varray(10) of integer;'
        sql_res6 = self.pri_sh.execut_db_sql(sql_cmd6, dbname=self.dump_db_01)
        self.log.info(sql_res6)
        self.assertIn(self.constant.CREATE_TYPE_SUCCESS_MSG, sql_res6,
                      '创建失败')
        sql_cmd7 = f'create type varray_type_0030_3 as varray(10) of boolean;'
        sql_res7 = self.pri_sh.execut_db_sql(sql_cmd7, dbname=self.dump_db_01)
        self.log.info(sql_res7)
        self.assertIn(self.constant.CREATE_TYPE_SUCCESS_MSG, sql_res7,
                      '创建失败')
        sql_cmd8 = f'create type varray_type_0030_4 as varray(10) of date;'
        sql_res8 = self.pri_sh.execut_db_sql(sql_cmd8, dbname=self.dump_db_01)
        self.log.info(sql_res8)
        self.assertIn(self.constant.CREATE_TYPE_SUCCESS_MSG, sql_res8,
                      '创建失败')
        sql_cmd9 = f'create type varray_type_0030_5 as varray(10) of time;'
        sql_res9 = self.pri_sh.execut_db_sql(sql_cmd9, dbname=self.dump_db_01)
        self.log.info(sql_res9)
        self.assertIn(self.constant.CREATE_TYPE_SUCCESS_MSG, sql_res9,
                      '创建失败')
        sql_cmd10 = f'create type varray_type_0030_6 as ' \
                   f'varray(10) of varchar(200);'
        sql_res10 = self.pri_sh.execut_db_sql(sql_cmd10, dbname=self.dump_db_01)
        self.log.info(sql_res10)
        self.assertIn(self.constant.CREATE_TYPE_SUCCESS_MSG, sql_res10,
                      '创建失败')
        sql_cmd11 = f'create or replace type type_0030_1 as (' \
                    f'c1 varray_type_0030_1, c2 varray_type_0030_2, ' \
                    f'c3 varray_type_0030_3, c4 varray_type_0030_4,' \
                    f'c5 varray_type_0030_5, c6 varray_type_0030_6);'
        sql_res11 = self.pri_sh.execut_db_sql(sql_cmd11, dbname=self.dump_db_01)
        self.log.info(sql_res11)
        self.assertIn(self.constant.CREATE_TYPE_SUCCESS_MSG,
                      sql_res11, '创建失败')
        sql_cmd12 = f'create or replace type tb_type_0030_1 ' \
                    f'as table of type_0030_1;'
        sql_res12 = self.pri_sh.execut_db_sql(sql_cmd12, dbname=self.dump_db_01)
        self.log.info(sql_res12)
        self.assertIn(self.constant.CREATE_TYPE_SUCCESS_MSG,
                      sql_res12, '创建失败')

        self.log.info('-----step3:使用gs_dump导出函数；expect：成功-----')
        sql13 = """create or replace function 
                   func_pipelined_0030(count in number)
                   returns tb_type_0030 pipelined
                   as \$\$
                   declare result type_0030;
                   begin
                   for i in 1 .. count loop
                   result.c1 = i + 1;
                   result.c2 = i + 1;
                   result.c3 = i + 0.005;
                   result.c4 = i - 0.5;
                   result.c5 = i + 0.7;
                   result.c6 = i * 0.5;
                   result.c7 = 1;
                   result.c8 = (i / 0.5);
                   result.c9 = true;
                   result.c10 = '1sadf1234';
                   result.c11 = 'sdfwdf';
                   result.c12 = '123wsdfwaerwefasd';
                   result.c13 = 'sdfwersdf';
                   result.c14 = 'exdgeww';
                   result.c15 = 'a'::blob;
                   result.c16 = current_date;
                   result.c17 = current_time;
                   result.c18 = age(timestamp '2001-04-10', 
                   timestamp '1957-06-13');
                   pipe row(result);
                   end loop;
                   return;
                   end;
                   \$\$ language plpgsql;"""
        sql_res13 = self.pri_sh.execut_db_sql(sql13, dbname=self.dump_db_01)
        self.log.info(sql_res13)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG, sql_res13)

        sql14 = r"""CREATE OR REPLACE PACKAGE pkg0030 AS
                   TYPE tb_type_0030 AS TABLE OF type_0030;
                   FUNCTION func_pipelined_0030(count NUMBER) 
                   RETURN tb_type_0030 PIPELINED;
                   END pkg0030;"""
        sql_res14 = self.pri_sh.execut_db_sql(sql14, dbname=self.dump_db_01)
        self.log.info(sql_res14)
        self.assertIn(self.constant.create_package_success_msg, sql_res14)

        sql15 = r"""CREATE OR REPLACE PACKAGE BODY pkg0030 AS
                   FUNCTION func_pipelined_0030(count NUMBER) 
                   RETURN tb_type_0030 PIPELINED IS
                   declare result type_0030;
                       BEGIN
                           FOR i IN 1..count LOOP
                               result.c1 = i + 1;
                               result.c2 = i + 1;
                               result.c3 = i + 0.005;
                               result.c4 = i - 0.5;
                               result.c5 = i + 0.7;
                               result.c6 = i * 0.5;
                               result.c7 = 1;
                               result.c8 = (i / 0.5);
                               result.c9 = true;
                               result.c10 = '1sadf1234';
                               result.c11 = 'sdfwdf';
                               result.c12 = '123wsdfwaerwefasd';
                               result.c13 = 'sdfwersdf';
                               result.c14 = 'exdgeww';
                               result.c15 = 'a'::blob;
                               result.c16 = current_date;
                               result.c17 = current_time;
                               result.c18 = age(timestamp '2015-04-10', 
                               timestamp '1957-06-13');
                               pipe row(result);
                           END LOOP;
                           RETURN;
                       END;
                       END pkg0030;"""
        sql_res15 = self.pri_sh.execut_db_sql(sql15, dbname=self.dump_db_01)
        self.log.info(sql_res15)
        self.assertIn(self.constant.create_package_body_success_msg, sql_res15)

        dump_cmd16 = f'source {macro.DB_ENV_PATH};' \
                   f'gs_dump {self.dump_db_01} -p ' \
                   f'{self.dbuser_node.db_port} -F c -f ' \
                   f'{self.dump_path};'
        self.log.info(dump_cmd16)
        cmd_res16 = self.common.get_sh_result(self.dbuser_node, dump_cmd16)
        self.log.info(cmd_res16)
        self.assertIn(self.constant.GS_DUMP_SUCCESS_MSG, cmd_res16, '备份失败')

        self.log.info('-----step4:使用gs_restore恢复函数；expect：成功-----')
        dump_cmd17 = f'source {macro.DB_ENV_PATH};' \
                      f'gs_restore -d {self.dump_db_02} -p ' \
                      f'{self.dbuser_node.db_port} -c {self.dump_path}'
        self.log.info(dump_cmd17)
        cmd_res17 = self.common.get_sh_result(self.dbuser_node, dump_cmd17)
        self.log.info(cmd_res17)
        self.assertIn(self.constant.RESTORE_SUCCESS_MSG, cmd_res17, '恢复失败')

        self.log.info('-----step5:调用pipeliend函数；expect：成功-----')
        execute_cmd = f'select * from table(func_pipelined_0030(1));' \
                      f'select * from pkg0030.func_pipelined_0030(1);'
        res = self.pri_sh.execut_db_sql(execute_cmd,
                                        dbname=self.dump_db_02)
        self.log.info(res)
        self.assertIn('43 years 9 mons 27 days', res, '函数调用失败')
        self.assertIn('57 years 9 mons 27 days', res, '函数调用失败')

    def tearDown(self):
        self.log.info('-----step6:清理环境；expect：成功-----')
        del_cmd = f'rm -rf {self.dump_path}'
        del_res = self.common.get_sh_result(self.root_node, del_cmd)
        sql1 = f'drop database if exists {self.dump_db_01};'
        sql2 = f'drop database if exists {self.dump_db_02};'
        res2 = self.pri_sh.execut_db_sql(sql1)
        self.log.info(res2)
        res3 = self.pri_sh.execut_db_sql(sql2)
        self.log.info(res3)
        self.assertEqual('', del_res, '文件删除失败')
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, res2, '数据库删除失败')
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, res3, '数据库删除失败')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
